@echo off
chcp 65001 >nul
setlocal EnableDelayedExpansion

REM 数据库恢复脚本 (Windows)
REM Database Restore Script for Delivery Management System

set "DB_PATH=..\data\delivery-management.db"
set "BACKUP_PATH=.\backup\full-backup.sql"
set "SCHEMA_PATH=.\schema\schema.sql"
set "DATA_DIR=.\data"

echo ================================================
echo    交付管理系统数据库恢复脚本
echo    Delivery Management System Database Restore Script
echo ================================================
echo.

REM 检查sqlite3是否可用
sqlite3 -version >nul 2>&1
if errorlevel 1 (
    echo [错误] sqlite3 未找到。请确保 sqlite3.exe 在系统 PATH 中。
    echo [Error] sqlite3 not found. Please ensure sqlite3.exe is in system PATH.
    echo.
    echo 您可以从以下地址下载 sqlite3:
    echo You can download sqlite3 from:
    echo https://www.sqlite.org/download.html
    pause
    exit /b 1
)

echo 请选择恢复方式:
echo Please choose restore method:
echo.
echo 1) 完整恢复 (删除现有数据库并从完整备份恢复)
echo    Full restore (delete existing database and restore from full backup)
echo.
echo 2) 仅恢复结构 (创建空数据库结构)
echo    Schema only (create empty database structure)
echo.
echo 3) 仅恢复核心数据 (恢复用户、角色、元数据等核心表)
echo    Core data only (restore users, roles, metadata tables)
echo.
echo 4) 自定义恢复 (选择特定数据文件)
echo    Custom restore (choose specific data files)
echo.
echo 5) 退出 / Exit
echo.

set /p choice="请输入选项 (1-5) / Enter option (1-5): "

if "%choice%"=="1" goto full_restore
if "%choice%"=="2" goto schema_only
if "%choice%"=="3" goto core_data
if "%choice%"=="4" goto custom_restore
if "%choice%"=="5" goto exit
echo [错误] 无效选项
goto exit

:full_restore
echo.
echo [信息] 执行完整恢复...
echo [Info] Performing full restore...

REM 备份现有数据库
if exist "%DB_PATH%" (
    echo 备份现有数据库...
    for /f "tokens=1-4 delims=/ " %%i in ('date /t') do set mydate=%%l%%j%%k
    for /f "tokens=1-2 delims=: " %%i in ('time /t') do set mytime=%%i%%j
    set mytime=!mytime: =0!
    copy "%DB_PATH%" "%DB_PATH%.backup.!mydate!_!mytime!"
)

REM 删除现有数据库
if exist "%DB_PATH%" del "%DB_PATH%"

REM 从完整备份恢复
echo 从完整备份恢复数据库...
sqlite3 "%DB_PATH%" < "%BACKUP_PATH%"

if errorlevel 1 (
    echo [错误] 完整恢复失败
    goto exit
)

echo [成功] 完整恢复完成！
goto integrity_check

:schema_only
echo.
echo [信息] 仅创建数据库结构...
echo [Info] Creating database schema only...

REM 备份现有数据库
if exist "%DB_PATH%" (
    echo 备份现有数据库...
    for /f "tokens=1-4 delims=/ " %%i in ('date /t') do set mydate=%%l%%j%%k
    for /f "tokens=1-2 delims=: " %%i in ('time /t') do set mytime=%%i%%j
    set mytime=!mytime: =0!
    copy "%DB_PATH%" "%DB_PATH%.backup.!mydate!_!mytime!"
)

REM 删除现有数据库
if exist "%DB_PATH%" del "%DB_PATH%"

REM 仅恢复结构
sqlite3 "%DB_PATH%" < "%SCHEMA_PATH%"

if errorlevel 1 (
    echo [错误] 结构创建失败
    goto exit
)

echo [成功] 数据库结构创建完成！
goto integrity_check

:core_data
echo.
echo [信息] 恢复核心数据...
echo [Info] Restoring core data...

REM 确保数据库存在
if not exist "%DB_PATH%" (
    echo 数据库不存在，先创建结构...
    sqlite3 "%DB_PATH%" < "%SCHEMA_PATH%"
)

REM 清空核心表
echo 清空核心表...
sqlite3 "%DB_PATH%" "DELETE FROM users; DELETE FROM roles; DELETE FROM parameter_field_metadata; DELETE FROM parameter_table_metadata; DELETE FROM devices;"

REM 恢复核心数据
echo 恢复用户和角色数据...
sqlite3 "%DB_PATH%" < "%DATA_DIR%\roles.sql"
sqlite3 "%DB_PATH%" < "%DATA_DIR%\users.sql"

echo 恢复元数据...
sqlite3 "%DB_PATH%" < "%DATA_DIR%\parameter_field_metadata.sql"
sqlite3 "%DB_PATH%" < "%DATA_DIR%\parameter_table_metadata.sql"

echo 恢复设备数据...
sqlite3 "%DB_PATH%" < "%DATA_DIR%\devices.sql"

echo [成功] 核心数据恢复完成！
goto integrity_check

:custom_restore
echo.
echo [信息] 自定义恢复模式
echo [Info] Custom restore mode
echo.
echo 可用的数据文件 / Available data files:
dir /b "%DATA_DIR%\*.sql" 2>nul
if errorlevel 1 echo 没有找到数据文件 / No data files found

echo.
echo 请输入要恢复的SQL文件名 (不包含路径，多个文件用空格分隔):
echo Enter SQL filenames to restore (without path, separate multiple files with spaces):
set /p files="> "

REM 确保数据库存在
if not exist "%DB_PATH%" (
    echo 数据库不存在，先创建结构...
    sqlite3 "%DB_PATH%" < "%SCHEMA_PATH%"
)

REM 恢复指定文件
for %%f in (%files%) do (
    if exist "%DATA_DIR%\%%f" (
        echo 恢复 %%f...
        sqlite3 "%DB_PATH%" < "%DATA_DIR%\%%f"
    ) else (
        echo [警告] 文件 %%f 不存在
    )
)

echo [成功] 自定义恢复完成！
goto integrity_check

:integrity_check
echo.
echo 检查数据库完整性...
sqlite3 "%DB_PATH%" "PRAGMA integrity_check;" | findstr "ok" >nul
if errorlevel 1 (
    echo [错误] 数据库完整性检查失败！
    goto exit
)

echo [成功] 数据库完整性检查通过！

REM 显示数据库统计信息
echo.
echo ================================================
echo 数据库统计信息 / Database Statistics:
echo ================================================

REM 显示文件大小
for %%A in ("%DB_PATH%") do set size=%%~zA
echo 数据库文件大小 / Database file size: !size! bytes

REM 显示表数量
for /f %%i in ('sqlite3 "%DB_PATH%" ".tables" ^| find /c /v ""') do set table_count=%%i
echo 表数量 / Number of tables: !table_count!

REM 显示用户数量
for /f %%i in ('sqlite3 "%DB_PATH%" "SELECT COUNT(*) FROM users;" 2^>nul') do set user_count=%%i
if "!user_count!"=="" set user_count=0
echo 用户数量 / Number of users: !user_count!

REM 显示设备数量
for /f %%i in ('sqlite3 "%DB_PATH%" "SELECT COUNT(*) FROM devices;" 2^>nul') do set device_count=%%i
if "!device_count!"=="" set device_count=0
echo 设备数量 / Number of devices: !device_count!

echo.
echo [成功] 数据库恢复操作完成！
echo [Success] Database restore operation completed!

:exit
echo.
pause